/** <a href="http://www.cpupk.com/decompiler">Eclipse Class Decompiler</a> plugin, Copyright (c) 2017 Chen Chao. **/
package com.minierp.dao.hibernate;

import com.minierp.dao.StafferDao;
import com.minierp.dao.hibernate.BaseDaoImpl;
import com.minierp.model.Staffer;
import java.sql.SQLException;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

public class StafferDaoImpl extends BaseDaoImpl implements StafferDao {
	public List getList(long departmentId, boolean justEnabled,
			boolean includeSub, String searchKey, String orderBy) {
		String sql = "select s from com.minierp.model.Staffer s";
		String[] paramNames = (String[]) null;
		Object[] values = (Object[]) null;
		String whereStr;
		if (departmentId != 0L) {
			sql = sql + ",com.minierp.model.Department d";
			if (includeSub) {
				whereStr = " s.departmentId=d.id and (d.id=" + departmentId
						+ " or (d.parentIdStr like \'%;" + departmentId
						+ ";%\'))";
			} else {
				whereStr = " s.departmentId=d.id and s.departmentId="
						+ departmentId;
			}
		} else {
			whereStr = " 1=1";
		}

		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			String nameSpell = searchKey.toUpperCase();
			whereStr = whereStr
					+ " and ((s.stafferName like :stafferName) or (s.stafferCode like :stafferCode) or (s.nameSpell like :nameSpell) )";
			paramNames = new String[]{"stafferName", "stafferCode", "nameSpell"};
			values = new Object[]{"%" + searchKey + "%", "%" + searchKey + "%",
					"%" + nameSpell + "%"};
		}

		if (justEnabled) {
			whereStr = whereStr + " and s.enabled=\'Y\'";
		}

		sql = sql + " where " + whereStr;
		if (orderBy != null && orderBy.equals("NAME")) {
			sql = sql + " order by s.nameSpell,s.stafferName";
		} else {
			sql = sql
					+ " order by s.enabled desc,s.departmentId desc,s.nameSpell desc";
		}

		return paramNames != null ? this.getHibernateTemplate()
				.findByNamedParam(sql, paramNames, values) : this
				.getHibernateTemplate().find(sql);
	}

	public List getAllChildren(long superiorId, boolean justEnabled) {
		String sql = "from com.minierp.model.Staffer as obj where (obj.superiorIdStr like :superiorIdStr)";
		if (justEnabled) {
			sql = sql + " and obj.enabled=\'Y\'";
		}

		sql = sql + " order by obj.nameSpell,obj.stafferName";
		String superiorIdStr = "%;" + superiorId + ";%";
		String[] paramNames = new String[]{"superiorIdStr"};
		Object[] values = new Object[]{superiorIdStr};
		return this.getHibernateTemplate().findByNamedParam(sql, paramNames,
				values);
	}

	public int delete(final long id) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "delete com.minierp.model.Staffer where id=:id";
						return new Integer(session.createQuery(hql)
								.setLong("id", id).executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public Staffer get(String stafferCode) {
		String sql = "from com.minierp.model.Staffer as obj where obj.stafferCode = :stafferCode order by obj.id";
		String[] paramNames = new String[]{"stafferCode"};
		Object[] values = new Object[]{stafferCode};
		List list = this.getHibernateTemplate().findByNamedParam(sql,
				paramNames, values);
		return list.isEmpty() ? null : (Staffer) list.get(0);
	}

	public int getCount(long departmentId) {
		String sql = "select count(s.id) from com.minierp.model.Staffer s ,com.minierp.model.Department d where s.departmentId=d.id and s.enabled=\'Y\' and (d.id="
				+ departmentId
				+ " or (d.parentIdStr like \'%;"
				+ departmentId
				+ ";%\'))";
		List list = this.getHibernateTemplate().find(sql);
		return list.isEmpty() ? 0 : Integer.parseInt(list.get(0).toString());
	}

	public List getSmsList() {
		String sql = "from com.minierp.model.Staffer s where s.enabled=\'Y\' and (s.mobile is not null) and s.mobile!=\'\' order by s.nameSpell,s.stafferName";
		return this.getHibernateTemplate().find(sql);
	}

	public List getList(long departmentId, String stafferName) {
		String sql = "select s.id,s.staffer_name,s.possie,d.department_name from staffer s left join department d on s.department_id=d.id where 1=1";
		if (departmentId != 0L) {
			sql = sql + " and (d.id=" + departmentId
					+ " or (d.parent_id_str like \'%;" + departmentId
					+ ";%\'))";
		}

		if (stafferName != null) {
			stafferName = stafferName.trim();
		}

		if (stafferName != null && !stafferName.equals("")) {
			sql = sql + " and (s.staffer_name like :stafferName)";
		}

		sql = sql + " order by s.name_spell,s.staffer_name";
		Session sess = null;

		List arg7;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (stafferName != null && !stafferName.equals("")) {
				q.setString("stafferName", "%" + stafferName + "%");
			}

			arg7 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg7;
	}

	public boolean isHasClient(long id) {
		String sql = "select count(e.id) from com.minierp.model.Establishment e where e.managerId="
				+ id + " and e.estType=\'CLIENT\'" + " and e.enabled=\'Y\'";
		List list = this.getHibernateTemplate().find(sql);
		int num = Integer.parseInt(list.get(0).toString());
		return num > 0;
	}

	public Staffer getByName(String stafferName) {
		String sql = "from com.minierp.model.Staffer as obj where obj.stafferName = :stafferName order by obj.id";
		String[] paramNames = new String[]{"stafferName"};
		Object[] values = new Object[]{stafferName};
		List list = this.getHibernateTemplate().findByNamedParam(sql,
				paramNames, values);
		return list.isEmpty() ? null : (Staffer) list.get(0);
	}

	public int getAppListForSelectCount(String stafName, String depaName) {
		String sql = "select count(*) from staffer s left join department d on s.department_id=d.id where s.enabled=\'Y\'";
		if (stafName != null && !stafName.equals("")) {
			sql = sql + " and (s.staffer_name like :stafName)";
		}

		if (depaName != null && !depaName.equals("")) {
			sql = sql + " and (d.department_name like :depaName)";
		}

		Session sess = null;

		int arg7;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (stafName != null && !stafName.equals("")) {
				q.setString("stafName", "%" + stafName + "%");
			}

			if (depaName != null && !depaName.equals("")) {
				q.setString("depaName", "%" + depaName + "%");
			}

			List list = q.list();
			if (list.isEmpty()) {
				return 0;
			}

			arg7 = Integer.parseInt(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg7;
	}

	public List getAppListForSelect(String stafName, String depaName,
			String orderBy, int startIndex, int num) {
		String sql = "select s.id,s.staffer_name,d.department_name from staffer s left join department d on s.department_id=d.id where s.enabled=\'Y\'";
		if (stafName != null && !stafName.equals("")) {
			sql = sql + " and (s.staffer_name like :stafName)";
		}

		if (depaName != null && !depaName.equals("")) {
			sql = sql + " and (d.department_name like :depaName)";
		}

		if (orderBy != null && orderBy.equals("DEPA")) {
			sql = sql + " order by d.department_name";
		} else {
			sql = sql + " order by s.name_spell,s.staffer_name";
		}

		Session sess = null;

		List arg9;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (stafName != null && !stafName.equals("")) {
				q.setString("stafName", "%" + stafName + "%");
			}

			if (depaName != null && !depaName.equals("")) {
				q.setString("depaName", "%" + depaName + "%");
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg9 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg9;
	}
}